import pandas as pd
import plotly.express as px
import numpy as np
import plotly.graph_objects as go
import matplotlib.pyplot as plt

#4 interactive scatter plot of average number of opiod pills by year plot using plotly


import sqlite3 as sq3
con = sq3.connect("csvs/opioid.db")

con = sq3.connect("csvs/opioid.db")

annual = pd.read_sql_query("SELECT * from annual", con)

con.close


#Use the annual dataframe

annual = annual.rename(columns={"count":"count_1"})
annual['count_1'] = annual['count_1'].astype(int)

year_06 = annual.loc[(annual.year == '2006')]
year_13 = annual.loc[(annual.year == '2013')]

avg_counts_by_year = []
n = annual.year.value_counts()
n= n.sort_index()
for i in range(9):
    my_year = annual.loc[(annual.year == str(i+2006))]
    print(i+2006)
    avg_counts_by_year.append(my_year.count_1.sum()/(n[i]))
    
print(avg_counts_by_year)


years = list(annual.year.unique())
years = [int(i) for i in years]

year_count = pd.DataFrame(years, columns=["Year"])
year_count = year_count.assign( Average_count =avg_counts_by_year)
fig_2 = px.scatter(year_count, x="Year",y="Average_count")
fig_2.show()

2006
2007
2008
2009
2010
2011
2012
2013
2014
[6725.041935483871, 7429.199546485261, 7946.208901884341, 8247.82385440364, 8871.797073170732, 9259.383717158611, 9226.517487046633, 9313.991896272286, 7972.25737439222]